from flask import Blueprint, request, jsonify, g
import sqlite3
from database import get_db, DatabaseConnector

database_bp = Blueprint('database', __name__)

@database_bp.route('/connections', methods=['GET'])
def get_connections():
    """获取所有数据库连接配置"""
    db = get_db()
    cursor = db.execute('SELECT * FROM db_connections ORDER BY name')
    connections = cursor.fetchall()
    
    result = []
    for conn in connections:
        result.append({
            'id': conn['id'],
            'name': conn['name'],
            'db_type': conn['db_type'],
            'host': conn['host'],
            'port': conn['port'],
            'database': conn['database'],
            'username': conn['username'],
            # 不返回密码，只返回是否已设置密码
            'has_password': len(conn['password']) > 0,
            'created_at': conn['created_at'],
            'updated_at': conn['updated_at']
        })
    
    return jsonify(result)

@database_bp.route('/connections', methods=['POST'])
def create_connection():
    """创建新的数据库连接配置"""
    data = request.json
    
    required_fields = ['name', 'db_type', 'host', 'database', 'username', 'password']
    for field in required_fields:
        if field not in data:
            return jsonify({'error': f'缺少必要字段: {field}'}), 400
    
    db = get_db()
    try:
        cursor = db.execute(
            '''INSERT INTO db_connections 
               (name, db_type, host, port, database, username, password) 
               VALUES (?, ?, ?, ?, ?, ?, ?)''',
            (data['name'], data['db_type'], data['host'], data.get('port'), 
             data['database'], data['username'], data['password'])
        )
        db.commit()
        
        connection_id = cursor.lastrowid
        return jsonify({'id': connection_id, 'message': '连接配置创建成功'}), 201
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'创建连接失败: {str(e)}'}), 500

@database_bp.route('/connections/<int:conn_id>', methods=['GET'])
def get_connection(conn_id):
    """获取指定ID的数据库连接配置"""
    db = get_db()
    conn = db.execute('SELECT * FROM db_connections WHERE id = ?', (conn_id,)).fetchone()
    
    if conn is None:
        return jsonify({'error': '连接配置不存在'}), 404
    
    result = {
        'id': conn['id'],
        'name': conn['name'],
        'db_type': conn['db_type'],
        'host': conn['host'],
        'port': conn['port'],
        'database': conn['database'],
        'username': conn['username'],
        'password': conn['password'],  # 这里返回密码，因为是编辑场景
        'created_at': conn['created_at'],
        'updated_at': conn['updated_at']
    }
    
    return jsonify(result)

@database_bp.route('/connections/<int:conn_id>', methods=['PUT'])
def update_connection(conn_id):
    """更新指定ID的数据库连接配置"""
    data = request.json
    
    db = get_db()
    conn = db.execute('SELECT * FROM db_connections WHERE id = ?', (conn_id,)).fetchone()
    
    if conn is None:
        return jsonify({'error': '连接配置不存在'}), 404
    
    try:
        db.execute(
            '''UPDATE db_connections SET 
               name = ?, db_type = ?, host = ?, port = ?, 
               database = ?, username = ?, password = ?,
               updated_at = CURRENT_TIMESTAMP
               WHERE id = ?''',
            (data.get('name', conn['name']), 
             data.get('db_type', conn['db_type']),
             data.get('host', conn['host']),
             data.get('port', conn['port']),
             data.get('database', conn['database']),
             data.get('username', conn['username']),
             data.get('password', conn['password']),
             conn_id)
        )
        db.commit()
        return jsonify({'message': '连接配置更新成功'})
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'更新连接失败: {str(e)}'}), 500

@database_bp.route('/connections/<int:conn_id>', methods=['DELETE'])
def delete_connection(conn_id):
    """删除指定ID的数据库连接配置"""
    db = get_db()
    conn = db.execute('SELECT * FROM db_connections WHERE id = ?', (conn_id,)).fetchone()
    
    if conn is None:
        return jsonify({'error': '连接配置不存在'}), 404
    
    try:
        db.execute('DELETE FROM db_connections WHERE id = ?', (conn_id,))
        db.commit()
        return jsonify({'message': '连接配置删除成功'})
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'删除连接失败: {str(e)}'}), 500

@database_bp.route('/connections/<int:conn_id>/test', methods=['GET'])
def test_connection_with_id(conn_id):
    """测试数据库连接是否有效"""
    db = get_db()
    conn = db.execute('SELECT * FROM db_connections WHERE id = ?', (conn_id,)).fetchone()
    
    if conn is None:
        return jsonify({'error': '连接配置不存在'}), 404
    
    connection_params = {
        'db_type': conn['db_type'],
        'host': conn['host'],
        'port': conn['port'],
        'database': conn['database'],
        'username': conn['username'],
        'password': conn['password']
    }
    
    connector = DatabaseConnector(connection_params)
    success = connector.connect()
    connector.close()
    
    if success:
        return jsonify({'status': 'success', 'message': '连接成功'})
    else:
        return jsonify({'status': 'error', 'message': '连接失败'}), 500

@database_bp.route('/query', methods=['POST'])
def execute_query():
    """执行SQL查询"""
    data = request.json
    
    if 'connection_id' not in data or 'query' not in data:
        return jsonify({'error': '缺少必要参数: connection_id 或 query'}), 400
    
    # 获取连接配置
    db = get_db()
    conn_config = db.execute(
        'SELECT * FROM db_connections WHERE id = ?', 
        (data['connection_id'],)
    ).fetchone()
    
    if conn_config is None:
        return jsonify({'error': '连接配置不存在'}), 404
    
    # 准备连接参数
    connection_params = {
        'db_type': conn_config['db_type'],
        'host': conn_config['host'],
        'port': conn_config['port'],
        'database': conn_config['database'],
        'username': conn_config['username'],
        'password': conn_config['password']
    }
    
    # 执行查询
    connector = DatabaseConnector(connection_params)
    result, error = connector.execute_query(data['query'])
    connector.close()
    
    # 记录查询历史
    status = 'success' if error is None else 'error'
    result_count = len(result) if isinstance(result, list) else None
    
    try:
        db.execute(
            '''INSERT INTO query_history 
               (connection_id, query_text, status, error_message, result_count) 
               VALUES (?, ?, ?, ?, ?)''',
            (data['connection_id'], data['query'], status, error, result_count)
        )
        db.commit()
    except Exception as e:
        print(f"记录查询历史失败: {str(e)}")
    
    if error:
        return jsonify({'error': error}), 500
    
    return jsonify({
        'result': result,
        'count': len(result) if isinstance(result, list) else None
    })

@database_bp.route('/query-history', methods=['GET'])
def get_query_history():
    """获取查询历史记录"""
    db = get_db()
    cursor = db.execute(
        '''SELECT h.*, c.name as connection_name, c.db_type 
           FROM query_history h
           JOIN db_connections c ON h.connection_id = c.id
           ORDER BY h.executed_at DESC
           LIMIT 100'''
    )
    history = cursor.fetchall()
    
    result = []
    for item in history:
        # 截断长查询文本，只显示前200个字符
        query_text = item['query_text']
        truncated_query = query_text if len(query_text) <= 200 else query_text[:200] + '...'
        
        result.append({
            'id': item['id'],
            'connection_id': item['connection_id'],
            'connection_name': item['connection_name'],
            'db_type': item['db_type'],
            'query_text': item['query_text'],
            'truncated_query': truncated_query,
            'executed_at': item['executed_at'],
            'execution_time': item['execution_time'],
            'result_count': item['result_count'],
            'status': item['status'],
            'error_message': item['error_message']
        })
    
    return jsonify(result)

@database_bp.route('/query-history/<int:history_id>', methods=['GET'])
def get_query_details(history_id):
    """获取特定查询的详细信息"""
    db = get_db()
    item = db.execute(
        '''SELECT h.*, c.name as connection_name, c.db_type 
           FROM query_history h
           JOIN db_connections c ON h.connection_id = c.id
           WHERE h.id = ?''',
        (history_id,)
    ).fetchone()
    
    if item is None:
        return jsonify({'error': '查询历史不存在'}), 404
    
    return jsonify({
        'id': item['id'],
        'connection_id': item['connection_id'],
        'connection_name': item['connection_name'],
        'db_type': item['db_type'],
        'query_text': item['query_text'],
        'executed_at': item['executed_at'],
        'execution_time': item['execution_time'],
        'result_count': item['result_count'],
        'status': item['status'],
        'error_message': item['error_message']
    })

@database_bp.route('/query-history', methods=['DELETE'])
def clear_query_history():
    """清空查询历史记录"""
    db = get_db()
    try:
        db.execute('DELETE FROM query_history')
        db.commit()
        return jsonify({'message': '查询历史已清空'})
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'清空查询历史失败: {str(e)}'}), 500

@database_bp.route('/connections/test', methods=['POST'])
def test_connection_without_id():
    """测试数据库连接"""
    data = request.json
    
    required_fields = ['db_type', 'host', 'database', 'username']
    for field in required_fields:
        if field not in data:
            return jsonify({'success': False, 'error': f'缺少必要字段: {field}'}), 400
    
    # 创建临时连接器
    try:
        connector = DatabaseConnector(
            db_type=data['db_type'],
            host=data['host'],
            port=data.get('port', ''),
            database=data['database'],
            username=data['username'],
            password=data.get('password', '')
        )
        
        # 测试连接
        connector.connect()
        connector.disconnect()
        
        return jsonify({'success': True, 'message': '连接成功'})
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
